﻿create procedure spMomSpecImport
	@FileName nvarchar(4000),
	@TemplateFile nvarchar(4000),
	@TransformProcedure varchar(50),
	@OwnerID int
as
set nocount on
declare @Message table ( LineNumber int, [Message] nvarchar(500) )

declare @ImportBatchID int
insert into tblImportBatch ( StartDate, FilePath, OwnerID ) values ( getdate(), @FileName, @OwnerID )
set @ImportBatchID = SCOPE_IDENTITY()

declare @Imported int
set @Imported = 0

-- Import the file into the table impMomSpec
begin try
	truncate table impMomSpec
	exec dbo.ImportFromFile 'impMomSpec', @FileName, @TemplateFile
end try
begin catch
	insert into @Message ( LineNumber, [Message] )
	values ( 0, left(ERROR_MESSAGE(), 500) )

	select * from @Message
	return 0
end catch

if @TransformProcedure is not null
	exec @TransformProcedure

update tblImportBatch set Lines = (select count(*) from impMomSpec)
where ImportBatchID = @ImportBatchID

insert into codeIDSource ( IDSourceCode, OwnerID, Description )
select 'F', @OwnerID, 'MOM alt_ord' where not exists ( select 1 from codeIDSource where IDSourceCode = 'F' and OwnerID = @OwnerID )
insert into codeIDSource ( IDSourceCode, OwnerID, Description )
select 'D', @OwnerID, 'MOM altnum' where not exists ( select 1 from codeIDSource where IDSourceCode = 'D' and OwnerID = @OwnerID )

begin transaction

declare @startId int
select @startId = min(MomSpecID) from impMomSpec

-- Initially clear the matching imports out of impOrders
delete from T2
from impMomSpec T1
	join impOrders T2 on T1.ORD_NUM = T2.SourceOrderID
where T2.OwnerID = @OwnerID

declare @id int
declare @impImportID int
declare @SourceOrderID varchar(50)
declare @SourceCustomerID varchar(50)
declare cImport cursor local for select MomSpecID from impMomSpec order by MomSpecID
open cImport
fetch next from cImport into @id
declare @fetch_status int
set @fetch_status = @@fetch_status
while @fetch_status = 0
begin
	declare @LineNumber int
	set @LineNumber = @id - @startId + 1

	if ( select coalesce(Continued, 'N') from impMomSpec where MomSpecID = @id ) not in ( 'X', 'Y' )
	begin
		select @SourceOrderID = ord_num,
			@SourceCustomerID = altnum
		from impMomSpec where MomSpecID = @id

		insert into impOrders( 
			SourceOrderID, 
			SourceCustomerID,
			FirstName,
			MiddleName,
			LastName,
			Company,
			Address1,
			Address2,
			Address3,
			City,
			State,
			PostalCode,
			Country,
			Phone,
			Email,
			[Language],
			OrderDate,
			OrderSource,
			PreferredShipMethod,
			PaidAmount,
			CardNumber,
			CardExpiration,
			CardCVV2,
			CardAuthorization,
			CardAuthorizationDate,
			CardResponseCode,
			AvsResponse,
			Cvv2Response,
			BankAccountNumber,
			BankRoutingNumber,
			ShipToFirstName,
			ShipToLastName,
			ShipToCompany,
			ShipToAddress1,
			ShipToAddress2,
			ShipToAddress3,
			ShipToCity,
			ShipToState,
			ShipToPostalCode,
			ShipToCountry,
			Shipping,
			QtyOrdered,
			ItemCode,
			UnitPrice,
			UnitDiscount,
			OwnerID )
		select @SourceOrderID,
			altnum,
			FirstName,
			null,
			LastName,
			Company,
			Address1,
			Address2,
			null,
			City,
			State,
			Zipcode,
			case coalesce(Country, '001') when '001' then 'United States of America' else Country end,
			Phone,
			email,
			'English',
			order_date,
			source_key,
			ShipVia,
			Paid,
			CardNum,
			Expires,
			null,
			Approval,
			Auth_Time,
			RCode,
			AVS,
			null,
			AccountNum,
			RoutingNum,
			SFirstName,
			SLastName,
			SCompany,
			SAddress1,
			SAddress2,
			null,
			SCity,
			SState,
			SZip,
			case coalesce(SCountry, '001') when '001' then 'United States of America' else SCountry end,
			case when UseShipAmt in ('Y', 'X') then Shipping else null end,
			quantity01,
			Product01,
			case when UsePrices in ('Y', 'X') then Price01 else 0 end,
			case when UsePrices in ('Y', 'X') then cast(Discount01 as float) / 100  else 1 end,
			@OwnerID
		from impMomSpec
		where MomSpecID = @id

		set @impImportID = SCOPE_IDENTITY()
	end
	else
	begin
		insert into impOrders( 
			SourceOrderID, 
			PreferredShipMethod,
			QtyOrdered,
			ItemCode,
			UnitPrice,
			UnitDiscount,
			OwnerID )
		select @SourceOrderID,
			ShipVia,
			quantity01,
			Product01,
			case when UsePrices in ('Y', 'X') then Price01 else null end,
			case when UsePrices in ('Y', 'X') then cast(Discount01 as float) / 100 else null end,
			@OwnerID
		from impMomSpec
		where MomSpecID = @id
			and Product01 is not null
	end

	insert into impOrders( 
		SourceOrderID, 
		PreferredShipMethod,
		QtyOrdered,
		ItemCode,
		UnitPrice,
		UnitDiscount,
		OwnerID )
	select @SourceOrderID,
		ShipVia,
		quantity02,
		Product02,
		case when UsePrices in ('Y', 'X') then Price02 else null end,
		case when UsePrices in ('Y', 'X') then cast(Discount02 as float) / 100 else null end,
		@OwnerID
	from impMomSpec
	where MomSpecID = @id
		and Product02 is not null

	insert into impOrders( 
		SourceOrderID, 
		PreferredShipMethod,
		QtyOrdered,
		ItemCode,
		UnitPrice,
		UnitDiscount,
		OwnerID )
	select @SourceOrderID,
		ShipVia,
		quantity03,
		Product03,
		case when UsePrices in ('Y', 'X') then Price03 else null end,
		case when UsePrices in ('Y', 'X') then cast(Discount03 as float) / 100 else null end,
		@OwnerID
	from impMomSpec
	where MomSpecID = @id
		and Product03 is not null

	insert into impOrders( 
		SourceOrderID, 
		PreferredShipMethod,
		QtyOrdered,
		ItemCode,
		UnitPrice,
		UnitDiscount,
		OwnerID )
	select @SourceOrderID,
		ShipVia,
		quantity04,
		Product04,
		case when UsePrices in ('Y', 'X') then Price04 else null end,
		case when UsePrices in ('Y', 'X') then cast(Discount04 as float) / 100 else null end,
		@OwnerID
	from impMomSpec
	where MomSpecID = @id
		and Product04 is not null

	insert into impOrders( 
		SourceOrderID, 
		PreferredShipMethod,
		QtyOrdered,
		ItemCode,
		UnitPrice,
		UnitDiscount,
		OwnerID )
	select @SourceOrderID,
		ShipVia,
		quantity05,
		Product05,
		case when UsePrices in ('Y', 'X') then Price05 else null end,
		case when UsePrices in ('Y', 'X') then cast(Discount05 as float) / 100 else null end,
		@OwnerID
	from impMomSpec
	where MomSpecID = @id
		and Product05 is not null

	fetch next from cImport into @id
	set @fetch_status = @@fetch_status

	declare @result int

	if ( select coalesce(Continued, 'N') from impMomSpec where MomSpecID = @id ) not in ( 'X', 'Y' )
			or @@fetch_status <> 0
	begin
		-- Get the customer number from the altnum
		declare @CustomerID int
		set @CustomerID = null

		select @CustomerID = CustomerID
		from tblCustomerSourceID 
		where IDValue = cast(@SourceCustomerID as varchar(20))
			and IDSourceCode = 'D'
			and OwnerID = @OwnerID

		-- Make sure an order with a matching alt_ord doesn't exist
		declare @OrderID int
		set @OrderID = null
		if exists ( select 1 from tblOrderSourceID 
					where IDValue = cast(@SourceOrderID as varchar(20))
						and IDSourceCode = 'F'
						and OwnerID = @OwnerID 
					)
			insert into @Message ( LineNumber, [Message] )
				values ( @LineNumber, 'An order with a matching ordnum already exists in the database' )
		else
		begin
			save transaction spImportOrderTrans
			begin try
				insert into @Message ( LineNumber, [Message] )
					exec @result = spImportOrder
						@id = @impImportID,
						@LineNumber = @LineNumber, 
						@ImportBatchID = @ImportBatchID,
						@CustomerID = @CustomerID output,
						@OrderID = @OrderID output
			end try
			begin catch
				rollback transaction spImportOrderTrans

				insert into @Message ( LineNumber, [Message] ) values ( @LineNumber, ERROR_MESSAGE() + char(13) + char(10) + 'line ' + CAST( ERROR_LINE() as varchar ) )

				set @result = -1
			end catch
			
			if @result = 0
			begin
				set @Imported = @Imported + 1

				-- Create the CustomerSourceID if necessary
				insert into tblCustomerSourceID( CustomerID, IDSourceCode, OwnerID, IDValue )
				select @CustomerID, 'D', @OwnerID, @SourceCustomerID
				where not exists ( select 1 from tblCustomerSourceID
					where IDValue = cast(@SourceCustomerID as varchar(20))
						and OwnerID = @OwnerID
						and IDSourceCode = 'D' )

				-- Create the OrderSourceID
				insert into tblOrderSourceID ( OrderID, IDSourceCode, OwnerID, IDValue )
				values ( @OrderID, 'F', @OwnerID, @SourceOrderID )
			end	
		end
	end
end

commit transaction

-- Clear the imports out of impMomSpec
delete from T2
from impMomSpec T1
	join impOrders T2 on T1.ORD_NUM = T2.SourceOrderID
where T2.OwnerID = @OwnerID

insert into tblImportBatchMessage ( ImportBatchID, LineNumber, [Message] )
select @ImportBatchID, LineNumber, [Message] from @Message

update tblImportBatch set Imported = @Imported, CompleteDate=GETDATE() where ImportBatchID = @ImportBatchID

select * from @Message

return @ImportBatchID